﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using VivahMuhurth;

namespace VivahMuhurth.DBAccess
{
    /// <summary>
    /// Summary description for DBAccess
    /// </summary>
    public class DBAccess
    {
        private static string strConnectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();

        #region " Web Portal Methods "

        public bool InsertLoginMaster(string firstName, string lastName, string EmailId, string Password, bool IsAdmin, bool IsActive, DateTime ExpiryDate)
        {
            bool success = false;

            SqlParameter[] sqlParams = new SqlParameter[7];
            sqlParams[0] = new SqlParameter("@FirstName", firstName);
            sqlParams[1] = new SqlParameter("@LastName", lastName);
            sqlParams[2] = new SqlParameter("@EmailId", EmailId);
            sqlParams[3] = new SqlParameter("@Password", Password);
            sqlParams[4] = new SqlParameter("@IsAdmin", IsAdmin);
            sqlParams[5] = new SqlParameter("@IsActive", IsActive);
            sqlParams[6] = new SqlParameter("@ExpiryDate", ExpiryDate);

            int result = Convert.ToInt32(SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_InsertLoginMaster, sqlParams).ToString());


            if (result == 1)
                success = false;
            else
                success = true;

            return success;
        }

        public int UpdateProfilePhoto(int ProfilePhotoId,int LoginId, string ImageName, string ImagePath,bool IsDelete)
        {
            SqlParameter[] sqlParams = new SqlParameter[5];
            sqlParams[0] = new SqlParameter("@ProfilePhotoId", ProfilePhotoId);
            sqlParams[1] = new SqlParameter("@LoginId", LoginId);
            sqlParams[2] = new SqlParameter("@ImageName", ImageName);
            sqlParams[3] = new SqlParameter("@ImagePath", ImagePath);
            sqlParams[4] = new SqlParameter("@IsDelete", IsDelete);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdateProfilePhoto, sqlParams);
        }

        public DataTable GetSearchProfileList()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GetSearchProfileList);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }

        public  DataTable GetReligion()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETRELIGION);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetCast()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCAST);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetSubCast()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETSUBCAST);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetOccupation()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETOCCUPATION);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetEducation()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETEDUCATION);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetAgelist()
        {
            DataSet ds = null;

            try
            {
                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETAGELIST);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetHeightlist()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETHEIGHTLIST);

            try
            {
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetWeightlist()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETWEIGHTLIST);

            try
            {
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable GetLanguagelist()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETLANGUAGELIST);

            try
            {
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public  DataTable ValidateUser(string emailId,string password)
        {
            DataSet ds = null;

            try
            {
                SqlParameter[] sqlParams = new SqlParameter[2];
                sqlParams[0] = new SqlParameter("@EmailId", emailId);
                sqlParams[1] = new SqlParameter("@Password", password);

                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_VALIDATEUSER, sqlParams);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        public DataTable ForgotPasswordDetails(string emailId)
        {
            DataSet ds = null;

            try
            {
                SqlParameter[] sqlParams = new SqlParameter[1];
                sqlParams[0] = new SqlParameter("@EmailId", emailId);

                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETFORGOTPASSWORD, sqlParams);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }

        #endregion

        #region " Search Profiles "
        public  DataTable GetProfiles(SearchParams objSearchParams)
        {
            DataSet ds = null;
            try
            {
                SqlParameter[] sqlParams = new SqlParameter[14];


                ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_SEARCHPROFILE, sqlParams);

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            finally
            {
                if (ds != null) { ds.Dispose(); ds = null; }
            }
        }
        #endregion

        #region " Get IDs "
        public int GetProfileBasicID(int loginId)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@LoginId", loginId);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETPROFILEBASICMASTERID, sqlParams);

            int ID = 0;
            if (result != null)
                ID = Convert.ToInt32(result);
            return ID;
        }
        public  int GetReligiosInfoID(int loginId)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@LoginId", loginId);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETRELIGIOUSINFOID, sqlParams);

            int ID = 0;
            if (result != null)
                ID = Convert.ToInt32(result);
            return ID;
        }
        public  int GetPhysicalInfoID(int loginId)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@LoginId", loginId);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETPHYSICALINFOID, sqlParams);

            int ID = 0;
            if (result != null)
                ID = Convert.ToInt32(result);
            return ID;
        }
        public  int GetCareerInfoID(int loginId)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@LoginId", loginId);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCAREERINFOMASTERID, sqlParams);

            int ID = 0;
            if (result != null)
                ID = Convert.ToInt32(result);
            return ID;
        }
        public  int GetFamityBackgroudID(int loginId)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@LoginId", loginId);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETFAMILYBACKGROUNDMASTERID, sqlParams);

            int ID = 0;
            if (result != null)
                ID = Convert.ToInt32(result);
            return ID;
        }
        #endregion

        #region " Update profile "
        public  int UpdateProfileBasicMaster(ProfileBasicMaster profileBasicMaster)
        {
            SqlParameter[] sqlParams = new SqlParameter[10];
            sqlParams[0] = new SqlParameter("@ProfileId", profileBasicMaster.ProfileId);
            sqlParams[1] = new SqlParameter("@LoginId", profileBasicMaster.LoginId);
            sqlParams[2] = new SqlParameter("@MotherToungeLanguageId", profileBasicMaster.MotherToungeLanguageId);
            sqlParams[3] = new SqlParameter("@Gender", profileBasicMaster.Gender);
            sqlParams[4] = new SqlParameter("@DateOfBirth", profileBasicMaster.DateOfBirth);
            sqlParams[5] = new SqlParameter("@ProfileCreateBy", profileBasicMaster.ProfileCreateBy);
            sqlParams[6] = new SqlParameter("@maritialStatus", profileBasicMaster.maritialStatus);
            sqlParams[7] = new SqlParameter("@Children", profileBasicMaster.Children);
            sqlParams[8] = new SqlParameter("@AboutMe", profileBasicMaster.AboutMe);
            sqlParams[9] = new SqlParameter("@PartnerPreference", profileBasicMaster.PartnerPreference);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdateProfileBasicMaster, sqlParams);
        }
        public  int UpdateReligiousInfoMaster(ReligiousInfoMaster religiousInfoMaster)
        {
            SqlParameter[] sqlParams = new SqlParameter[11];
            sqlParams[0] = new SqlParameter("@ReligiousInfoId", religiousInfoMaster.ReligiousInfoId);
            sqlParams[1] = new SqlParameter("@LoginId", religiousInfoMaster.LoginId);
            sqlParams[2] = new SqlParameter("@SunSignId", religiousInfoMaster.SunSignId);
            sqlParams[3] = new SqlParameter("@ReligionId", religiousInfoMaster.ReligionId);
            sqlParams[4] = new SqlParameter("@CastId", religiousInfoMaster.CastId);
            sqlParams[5] = new SqlParameter("@SubCastId", religiousInfoMaster.SubCastId);
            sqlParams[6] = new SqlParameter("@Gana", religiousInfoMaster.Gana);
            sqlParams[7] = new SqlParameter("@Gotra", religiousInfoMaster.Gotra);
            sqlParams[8] = new SqlParameter("@Nakshtra", religiousInfoMaster.Nakshtra);
            sqlParams[9] = new SqlParameter("@BirthTime", religiousInfoMaster.BirthTime);
            sqlParams[10] = new SqlParameter("@IsManglik", religiousInfoMaster.IsManglik);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdateReligiousInfoMaster, sqlParams);
        }
        public  int UpdateFamilyBackgroundMaster(FamilyBackGroundMaster familyBackGroundMaster)
        {
            SqlParameter[] sqlParams = new SqlParameter[14];
            sqlParams[0] = new SqlParameter("@FamilyBackGroundId", familyBackGroundMaster.FamilyBackGroundId);
            sqlParams[1] = new SqlParameter("@LoginId", familyBackGroundMaster.LoginId);
            sqlParams[2] = new SqlParameter("@FatherOccupationId", familyBackGroundMaster.FatherOccupationId);
            sqlParams[3] = new SqlParameter("@MotherOccupationId", familyBackGroundMaster.MotherOccupationId);
            sqlParams[4] = new SqlParameter("@FatherName", familyBackGroundMaster.FatherName);
            sqlParams[5]= new SqlParameter("@MotherName", familyBackGroundMaster.MotherName);
            sqlParams[6] = new SqlParameter("@NumberOfBrothers", familyBackGroundMaster.NumberOfBrothers);
            sqlParams[7] = new SqlParameter("@NumberOfBrothersMarried", familyBackGroundMaster.NumberOfBrothersMarried);
            sqlParams[8] = new SqlParameter("@NumberOfSisters", familyBackGroundMaster.NumberOfSisters);
            sqlParams[9] = new SqlParameter("@NumberOfSistersMarried", familyBackGroundMaster.NumberOfSistersMarried);
            sqlParams[10] = new SqlParameter("@IsJointFamily", familyBackGroundMaster.IsJointFamily);
            sqlParams[11] = new SqlParameter("@FamilyValues", familyBackGroundMaster.FamilyValues);
            sqlParams[12] = new SqlParameter("@KnownRelativesWithAddress", familyBackGroundMaster.KnownRelativesWithAddress);
            sqlParams[13] = new SqlParameter("@AboutMyFamily", familyBackGroundMaster.AboutMyFamily);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdateFamilyBackGroundMaster, sqlParams);
        }
        public  int UpdateCareerInfoMaster(CareerInfoMaster careerInfoMaster)
        {
            SqlParameter[] sqlParams = new SqlParameter[6];
            sqlParams[0] = new SqlParameter("@CareerInfoId", careerInfoMaster.CareerInfoId);
            sqlParams[1] = new SqlParameter("@LoginId", careerInfoMaster.LoginId);
            sqlParams[2] = new SqlParameter("@EducationId", careerInfoMaster.EducationId);
            sqlParams[3] = new SqlParameter("@OccupationId", careerInfoMaster.OccupationId);
            sqlParams[4] = new SqlParameter("@AnnualIncome", careerInfoMaster.AnnualIncome);
            sqlParams[5] = new SqlParameter("@RecidancyStatus", careerInfoMaster.RecidancyStatus);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdateCareerInfoMaster, sqlParams);
        }
        public  int UpdatePhysicalInfoMaster(PhysicalInfoMaster physicalInfoMaster)
        {
            SqlParameter[] sqlParams = new SqlParameter[12];
            sqlParams[0] = new SqlParameter("@PhysicalInfoId", physicalInfoMaster.PhysicalInfoId);
            sqlParams[1] = new SqlParameter("@LoginId", physicalInfoMaster.LoginId);
            sqlParams[2] = new SqlParameter("@Height", physicalInfoMaster.Height);
            sqlParams[3] = new SqlParameter("@Weight", physicalInfoMaster.Weight);
            sqlParams[4] = new SqlParameter("@BodyType", physicalInfoMaster.BodyType);
            sqlParams[5] = new SqlParameter("@BloodGroup", physicalInfoMaster.BloodGroup);
            sqlParams[6] = new SqlParameter("@complexion", physicalInfoMaster.complexion);
            sqlParams[7] = new SqlParameter("@PhysicallyChallengedStatus", physicalInfoMaster.PhysicallyChallengedStatus);
            sqlParams[8] = new SqlParameter("@DietType", physicalInfoMaster.DietType);
            sqlParams[9] = new SqlParameter("@IsSmoke", physicalInfoMaster.IsSmoke);
            sqlParams[10] = new SqlParameter("@IsDrink", physicalInfoMaster.IsDrink);
            sqlParams[11] = new SqlParameter("@IsSpects", physicalInfoMaster.IsSpects);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UpdatePhysicalInfoMaster, sqlParams);
        }
        #endregion

    }
}
